// var pg = require('pg');
var genericPool = require('generic-pool');
var _ = require('underscore');
var config = require('../lib/config');
var logger = require('./logger');
var mysql = require('mysql');
// var logger = require('../service/logging').logger;
// var Promise = require('bluebird'); 
var Q = require('q');

var pool  = mysql.createPool({
  connectionLimit : config.db.maxPoolSize,
  host            : config.db.host,
  user            : config.db.user,
  port            : config.db.port || 3306,
  password        : config.db.password,
  database        : config.db.database
});

exports.DB = DB = function(){
    // const factory = {
    //     create: function(){
    //          return new Promise(function(resolve, reject){
    //             var mysql      = require('mysql');
    //             var connection = mysql.createConnection({
    //               host     : config.db.host,
    //               user     : config.db.user,
    //               port     : config.db.port || 3306,
    //               password : config.db.password,
    //               database : config.db.database
    //             });
    //             connection.connect(function(err) {
    //               if (err) {
    //                 logger.error(err);
    //                 return;
    //               }
    //               resolve(connection);
    //             });
    //         })
    //     },
    //     destroy: function(client){
    //         return new Promise(function(resolve){
    //           client.on('end', function(){
    //             resolve()
    //           })
    //           client.disconnect()
    //         })
    //     }
    // };
    // const opts = {
    //     max: config.db.maxPoolSize, 
    //     min: config.db.minPoolSize, 
    //     idleTimeoutMillis: config.db.mysqlConnectTimeout
    // }
    // const myPool = genericPool.createPool(factory, opts);
    // const resourcePromise = myPool.acquire();
    this.query = function(sql,params,cb) {
        if(!_.isArray(params)){
            params = [];
        }
        if(config.db.print_sql){
            logger.info("[sqltext] " + sql);
            logger.info("[params] " + params);
        }
        // logger.info(pool);
        pool.getConnection(function(err,conn){
            if(err){
                logger.info(err);
                return cb(err);
            }
            conn.query(sql,params,function(err,results,fields){
                conn.release();
                if(err){
                    return cb(err);
                }
                return cb(null,results);
            })
        })
    };
    this.queryList = function(sql,params,cb){
        this.query(sql,params,cb);
    };
    this.queryInt = function(sql,params,cb){
        this.query(sql,params,function(err,rows){
            if(err) return cb(err);
            for(i in rows[0]){
                return cb(null,parseInt(rows[0][i]));
            }
            return cb(null,0);
        })
    };
    this.queryString = function(sql,params,cb){
        this.query(sql,params,function(err,rows){
            if(err) return cb(err);
            for(i in rows[0]){
                return cb(null,rows[0][i]);
            }
            return cb(null,"");
        })
    };
    /**
    ** limit: 每页多少条记录
    ** page : 页数
    **/
    this.queryPage = function(sql,params,page,limit,cb){
        var that = this; //可能遇到作用域问题，如果不用that
        var _sql = "select count(1) as counter from (" + sql + ") _sub";
        var result = {};
        page = page || 1;
        limit = limit || 20;
        that.query(_sql,params,function(err,rows){
            if(err){
                return cb(err);
            }
            result.count = parseInt(rows[0].counter);
            var start = (parseInt(page) -1 ) * limit;
            _sql = sql + " limit " + start + " , " + limit;
            that.query(_sql,params,function(err,rows){
                if (err) return cb(err);
                result.data = rows;
                result.pageSize = parseInt(limit);
                result.pageNum = parseInt(page);
                result.pageCount = Math.ceil(result.count/result.pageSize);
                return cb(null,result);
            })
        })
    };
    this.insert = function(table_name,fields,returning,cb){
        var i = 1;
        var params = [];
        var keys = [];
        var param_indexs = [];
        for(key in fields){
            param_indexs.push("?");
            keys.push(key);
            params.push(fields[key]);
        }
        var sql = "insert into " + table_name + "(" + keys.toString() + ") ";
        sql += " values(" + param_indexs.toString() + ") ";
        if(typeof(returning) == 'function'){
            cb = returning;
        }
        else if(returning){
            sql += " returning " + returning;
        }
        return this.query(sql,params,function(err,rows){
            if(err) return cb(err);
            return cb(null,rows[0]);
        })
    };
    this.update = function(table_name,fields,conditions,cb){
        var params = [];
        var i = 1;
        var par = [];
        var tmp_fields = [];
        for(key in fields){
            var _tmpstr = key + " = ?";
            params.push(fields[key]);  
            tmp_fields.push(_tmpstr);
        }
        var tmp_conditions = [];
        for(key in conditions){
            var _tmpstr = key + " = ?";
            params.push(conditions[key]);
            tmp_conditions.push(_tmpstr);
        }
        var sql = "update " + table_name + " set " + tmp_fields.toString();
        sql += " where " + tmp_conditions.join(" and ");
        console.log('update: '+sql);
        return this.query(sql,params,function(err,rows){
            if(err) return cb(err);
            return cb(null,rows);
        })
    }
    this.queryMap = function(sql,params,cb){
        this.query(sql,params,function(err,rows){
            if(err) return cb(err);
            if(rows.length > 0){
                return cb(null,rows[0]);
            }else{
                return cb(null,null);
            }
            
        })
    };
}
